package com.yunfei.utils;

import com.yunfei.api.user.result.LoginAdminReturnResult;
import oracle.jdbc.OracleTypes;

import java.sql.*;

public class Procedure {
    private Connection conn;
    private CallableStatement stat;
    private ResultSet rs;
    String url = "jdbc:oracle:thin:@10.0.100.223:1521:aiiidb";
    String driverName = "oracle.jdbc.driver.OracleDriver";
    String username = "aigroup";
    String password = "aigroup";
    String sql = "call load_model_data_pkg.sp_income(?,?)";
    String pkChkLoginSql = "call sp_check_login(?,?,?,?,?)";

    // 调用存储过程
    //load_model_data_pkg.sp_income(cust_id_in => :cust_id_in,
    //                                po_sp_rslt_fg => :po_sp_rslt_fg)
    public void callProcedure(String userId) {
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.prepareCall(sql);

            // 一个输入参数和三个输出参数
            stat.setString(1, userId);
            stat.registerOutParameter(2, OracleTypes.NUMBER);
            //stat.registerOutParameter(3, OracleTypes.NUMBER);
          //  stat.registerOutParameter(4, OracleTypes.VARCHAR);
            stat.execute();

           // String name = stat.getString(2);
            int sal = stat.getInt(2);
            //String job = stat.getString(4);

            System.out.println("sal: " + sal);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stat, rs);
        }
    }
    /**
    * @Description: 存储过程，填完理财目标的时候出发此存储过程
    * @Param: [url, driverName, username, password, userId]
    * @return: int 0成功其他失败
    * @Author: zhengyunfei
    * @Date: 2018/4/24
    * @time:13:49
    */
    public int callProcedure(String url,String driverName,String username,String password,String userId) {
        int result=-1;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.prepareCall(sql);
            // 一个输入参数和三个输出参数
            stat.setString(1, userId);
            stat.registerOutParameter(2, OracleTypes.NUMBER);
            stat.execute();
            result= stat.getInt(2);//输出结果，如果是0代表成功
            System.out.println("sal: " + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stat, rs);
        }
        return result;
    }
    /**
    * @Description: 校验用户名和密码是否正确
    * @return: 0 不正确（1:用户不存在,2:或者用户名或者密码错误）p_reasoncode = 0时  代表登录失败 错误信息在 p_reasoninfo展示
    * @return: p_reasoncode=1正确 代表登录成功 p_reasoninfo展示Y代表为客服部 展示N时代表非客服部
    * @Author: zhengyunfei
    * @Date: 2018/6/5
    * @time:16:58
    */
    public LoginAdminReturnResult callProcedureForCheckLogin(String url, String driverName,String jdbcUserName,String jdbcPassword, String username, String password) {
        int p_reasoncode=-1;//返回编码
        String p_reasoninfo="";//返回信息
        String p_emplname="";//登录账号的中文名称
        LoginAdminReturnResult returnResult=new LoginAdminReturnResult();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, jdbcUserName, jdbcPassword);
            stat = conn.prepareCall(pkChkLoginSql);
            // 2个输入参数和2个输出参数
            stat.setString(1, username);
            stat.setString(2, password);
            stat.registerOutParameter(3, OracleTypes.VARCHAR);
            stat.registerOutParameter(4, OracleTypes.VARCHAR);
            stat.registerOutParameter(5, OracleTypes.VARCHAR);
            stat.execute();
            p_reasoncode= stat.getInt(3);//输出结果，如果是0代表成功
            p_reasoninfo= stat.getString(4);//输出结果，如果是0代表成功
            p_emplname= stat.getString(5);//理财师中文名称
            returnResult.setpReasonCode(p_reasoncode);
            returnResult.setpReasonInfo(p_reasoninfo);
            returnResult.setpEmplName(p_emplname);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stat, rs);
        }
        return returnResult;
    }
    // 关闭连接
    public void close(Connection conn, CallableStatement stat, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stat = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }

    public static void main(String[] args) {
        new Procedure().callProcedure("20180417132146779447466");
    }
}